#!/usr/bin/env python
# -*- coding: utf-8

import json
import urllib
import urllib2
import sqlite3
import datetime # для datetime.datetime.now()
import sys # для sys.stdout.write()
import socket # для таймаута

def latest_exchanges_rate(cursor, params):
    """ получение из базы самых последних курсов валют относительно указанной в аргументе

    """
    print('call func ' + latest_exchanges_rate.__name__ + ' with params ', params)
    rows = cursor.execute('select sell, \
                                  min(for) gold_min,\
                                  avg(for) gold_avg,\
                                  sum(for*value)/sum(value) gold_super,\
                                  strftime(\'%s\',timestamp) st,\
                                  max(timestamp)\
                           from exchange\
                           where buy like ?\
                           group by sell\
                           having st=max(st)', 
                           params )
    res = {}
    for row in rows: res[row[0]] = row
#    for row in res: print(row, 7*' ', res[row][1], res[row][2], res[row][3], res[row][4], res[row][5])
    return res

def reload_goods_throw_json(url, cursor, str_ins_to_goods, now_day, rows, moneys, state, good, level):
    try_count = 0
    res = 0
    while try_count < 3 and res != 1:
        (f, res) = try_get_url(url)
        try_count = try_count + 1
    
    if res == 1:
       json_data = json.loads(f.read())
            # данных может не быть, проверка
       if len(json_data) > 1:
            json_data.pop() # удалить последний элемент list'a, т.к. это просто статистика
            for row in json_data:
                val_list = []
                for tag, list_d in row.iteritems():
                    val_list += [(val) for name, val in list_d.iteritems()]
                    
                price = float(val_list[3][:val_list[3].find(' ')])
                rec_1 = ( state, good, level, price,
                          int(val_list[4]), # quantity
                          int(val_list[1]), # company
                          val_list[2], # company name
                          price/rows[moneys[state]][1], 
                          price/rows[moneys[state]][2], 
                          price/rows[moneys[state]][3],
                          now_day[0]  )
                try:
                    cursor.execute( str_ins_to_goods, rec_1)
                except sqlite3.Error, e:
                    print "An error occurred:", e.args[0]
    else:
        print("get url " + url + " failed!")

def parse_market_files(cursor, now_day, moneys, states, goods, max_level, skip_mode):
    """ загрузка данных из полученных файлов в память
    
    """
    try:
        rows = latest_exchanges_rate(cursor, ('GOLD',) )
        str_ins_to_goods = 'insert into goods (state, good, level, price, \
                                    quantity, company, name,\
                                    gold_min, gold_avg, gold_super, timestamp) \
                                    values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        i = 0
        for state in states:
#            if moneys[state] in rows:
                for good in goods:
                    for level in range(1, max_level):
                        url = 'http://api.erepublik.com/v1/feeds/market/' + good + '/' + `level` + '/' + state + '.json'
                        print(url),
                        sys.stdout.flush() # допечатать вывод
                        reload_goods_throw_json(url, cursor, str_ins_to_goods, now_day, rows, moneys, state, good, level)
                        sys.stdout.write('\r' + len(url) * ' ' + '\r') # стереть вывод и переместиться в начало строки
        print('\n')
    except IOError, e:
        print(e)

def connect_to_base():
    """ подключение к базе, создание таблиц
    
    """
    connect = sqlite3.connect('db/erepa.db')
    cursor = connect.cursor()
    try:
        cursor.execute('create table goods \
        (id integer primary key AUTOINCREMENT, \
        state text, good text, level int, price real, \
        gold_min real, gold_avg real, gold_super real,\
        quantity int, company int, name text,\
        timestamp DEFAULT current_timestamp)')
    except sqlite3.Error, e:
        print "message on create table:", e.args[0]
    try:
        cursor.execute('create table exchange \
        (id integer primary key AUTOINCREMENT, \
        buy text, sell text, \
        is_a_citizen int, is_a_company int, is_an_organization int,\
        for real, value real, name text,\
        timestamp DEFAULT current_timestamp)')
    except sqlite3.Error, e:
        print "message on create table:", e.args[0]
    return (connect, cursor)

def test_goods_query(cursor, params):
    """ пробный запрос к товарам
    
    """
    cursor.execute('select state, \
                    round(min(gold_min/level), 4) p1, \
                    round(sum(gold_super/level*quantity)/sum(quantity), 4) p3, \
                    sum(quantity) s_quantity \
                    from goods \
                    where good like \'oil\' \
                        and timestamp = ? \
                    group by state \
                    order by p1', params)
    rows = cursor.fetchall()
#    for row in rows: print(row)
    print(`len(rows)`)

def test_exchange_query():
    (connect, cursor) = connect_to_base()
    #test_goods_query(cursor, now_day)
    rows = cursor.execute('select sell, timestamp, \
                                  round(min(for), 4) gold_min,\
                                  round(avg(for), 4) gold_avg,\
                                  round(sum(for*value)/sum(value), 4) gold_super\
                          from exchange\
                          group by sell\
                          having buy like ? and \
                                 cast( strftime( \'%s\', timestamp)  as int) = max( cast(strftime( \'%s\', timestamp) as int) )\
                          order by sell', ['GOLD'])
    rows = cursor.fetchall()
    for row in rows: print(row)
    print(`len(rows)`)
    
    cursor.close()
    connect.commit()
    connect.close()

def try_get_url(url_name):
    try:
        f = urllib2.urlopen(url_name) 
        return (f, 1);
    except:
        print("attempt get " + url_name + " failed")
        return (0, 0);

def load_exchange(cursor, now_day, buys, sells):
    """ загрузка курсов с валютного рынка. парсится напрямую через json, без xml
    
    """
    for buy in buys:
        for sell in sells:
            print(sell),
            sys.stdout.flush() # допечатать вывод
#            print('http://api.erepublik.com/v1/feeds/exchange/' + buy + '/' + sell + '.json')
            
            try_count = 0;
            res = 0
            url_name = 'http://api.erepublik.com/v1/feeds/exchange/' + buy + '/' + sell + '.json'
            while try_count < 3 and res != 1:
                (f, res) = try_get_url(url_name)
                try_count = try_count + 1
            
            if res == 1:
                json_data = json.loads(f.read())
                    # данных может не быть, проверка
                if len(json_data) > 1:
                    json_data.pop() # удалить последний элемент list'a, т.к. это просто статистика
                    str_query = "insert into exchange \
                        (timestamp, buy, sell, is_a_citizen, is_a_company, name, is_an_organization, for, value) \
                        values(?, ?, ?, ?, ?, ?, ?, ?, ?)"
                    for row in json_data:
                        val_list = []
                        for tag, list_d in row.iteritems():
                            val_list += [(val) for name, val in list_d.iteritems()]
                        val_list.pop(0) # удалить идущий первым url
                        val_list.insert(0, sell)
                        val_list.insert(0, buy)
                        val_list.insert(0, now_day[0])
                        cursor.execute(str_query, val_list)
                sys.stdout.write('\r' + len(sell) * ' ' + '\r') # стереть вывод и переместиться в начало строки
            else:
                print("get url " + url_name + " failed!")
                
def import_db():
    socket.setdefaulttimeout(5) # таймаут в секундах

    buys   = ['GOLD']
    sells  = ['ARS',      'ATS',     'AUD',      'BEF',     
              'BOB',      'BAM',     'BRL',      'BGN',     
              'CAD',      'CLP',     'CNY',      'COP',     
              'HRK',      'CZK',     'DKK',      'EEK',      
              'FIM',      'FRF',     'DEM',      'GRD',    
              'HUF',      'INR',     'IDR',      'IRR',     
              'IEP',      'NIS',     'ITL',      'JPY',      
              'LVL',      'LTL',     'MYR',      'MXN',
              'MDL',      'NLG',     'KPW',      'NOK',
              'PKR',      'PYG',     'PEN',      'PHP',
              'PLN',      'PTE',     'RON',      'RUB',     
              'RSD',      'SGD',     'SKK',      'SIT',     
              'ZAR',      'KRW',     'ESP',      'SEK',     
              'CHF',      'THB',     'TRY',      'UAH',      
              'GBP',      'UYU',     'USD',      'VEB']
    states = ['argentina',     'austria',                'australia',  'belgium', 
              'bolivia',       'bosnia-and-herzegovina', 'brazil',     'bulgaria',
              'canada',        'chile',                  'china',      'colombia',
              'croatia',       'czech-republic',         'denmark',    'estonia',  
              'finland',       'france',                 'germany',    'greece', 
              'hungary',       'india',                  'indonesia',  'iran',    
              'ireland',       'israel',                 'italy',      'japan',    
              'latvia',        'lithuania',              'malaysia',   'mexico', 
              'moldavia',      'netherlands',            'north-korea','norway', 
              'pakistan',      'paraguay',               'peru',       'philippines', 
              'poland',        'portugal',               'romania',    'russia',  
              'serbia',        'singapore',              'slovakia',   'slovenia',
              'south-africa',  'south-korea',            'spain',      'sweden',  
              'switzerland',   'thailand',               'turkey',     'ukraine',  
              'united-kingdom','uruguay',                'usa',        'venezuela']

    goods  = ['food',       'gift',         'weapon',       'ticket', 
              'grain',      'diamonds',     'iron',         'oil',
              'wood',       'house']#,      'hospital',     'defense'

# тестовый набор значений
#    sells  = ['ARS']
#    states = ['argentina']
#    goods  = ['gift']

        # значение даты для вставки в базу
    now_day = [datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')] 

        # "склеить" массивы валют и государств
    moneys={}
    for i in range(0, len(sells)): moneys[states[i]] = sells[i]

    (connect, cursor) = connect_to_base()
    load_exchange(cursor, now_day, buys, sells)
        # сбросить базу из памяти на диск, 
        # т.к. следующий запрос зависит от этих данных
    cursor.close()
    connect.commit()
    cursor = connect.cursor()

    parse_market_files(cursor, now_day, moneys, states, goods, 6, 0)
    test_goods_query(cursor, now_day)

    cursor.close()
    connect.commit()
    connect.close()

import_db()
#test_exchange_query()

